package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBProduct {
	
	private Connection con;
	
	// Contructor
	public DBProduct(){
		con = DbConnection1.getInstance().getDBcon();
	}
	
	// Get all products
	public ArrayList<Product> getAllProducts(boolean retrieveAssociation){
		return miscWhere("", retrieveAssociation);
	}

	// Find via productid
	public Product findProduct(String productId, boolean retrieveAssociation){
		String wClause = "productId = '" + productId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	// Search for productname
	public Product searchProductName(String productName, boolean retrieveAssociation){
		String wClause = "productName = '%" + productName + "%'";
		System.out.println("SeachName" + wClause);
		return singleWhere(wClause, retrieveAssociation);
	}
	
	// Insert new product
	
	public int insertProduct(Product product) throws Exception{
		int nextProductId = GetMax.getMaxId("Select max(productId) from Product");
		nextProductId = nextProductId++;
		System.out.println("next product =" + nextProductId);
		
		int rc = -1;
		String query="INSERT INTO product(productId, suppId, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock) Values('" +
				nextProductId + "','" +
				product.getSuppId() + "','" +
				product.getName() + "','" +
				product.getPurchasePrice() + "','" +
				product.getSalesPrice() + "','" +
				product.getRentPrice() + "','" +
				product.getCountryOfOrigin() + "','" +
				product.getMinStock() + ")";
		// Might be missing calls for the last attributes that can be null
		
		System.out.println("insert" + query);
		
		try{ // insert new employee +  dependent
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(query);
	          stmt.close();
	      }//end try
	       catch(SQLException ex){
	          System.out.println("Product not created");
	          throw new Exception ("Product is not inserted correct");
	       }
	       return(rc);
	}
	
	public int updateProduct(Product prod){
		Product prodObj = prod;
		
		int rc = -1;
		String query = "UPDATE product SET "+
				"suppId = '" + prodObj.getSuppId() + "', " +
				"name = '" + prodObj.getName() + "', "+
				"purchasePrice ='" + prodObj.getPurchasePrice() + "', " +
				"salesPrice = '" + prodObj.getSalesPrice() + "', " +
				"rentPrice = '" + prodObj.getRentPrice() + "', " +
				"countryOfOrigin = '" + prodObj.getCountryOfOrigin() + "', " +
				"minStock = '" + prodObj.getMinStock() +"' " +
				" WHERE productId = '"+ prodObj.getProductId() + "'";
				System.out.println("Update query:" + query);
				
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
		catch(Exception ex){
	 	 	System.out.println("Update exception in product db: "+ex);
	  	}
		return(rc);
	}
	
		public int delete(String productId){
			int rc = -1;
			
			String query="DELETE FROM product WHERE productId = '" +
					productId + "'";
	                System.out.println(query);
		  	try{ // delete from employee
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 	  	rc = stmt.executeUpdate(query);
		 	  	stmt.close();
	  		}//end try	
	   	        catch(Exception ex){
		 	  	System.out.println("Delete exception in product db: "+ex);
	   	        }
			return(rc);
		}
		
			
		private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
		{
	        ResultSet results;
		    ArrayList<Product> list = new ArrayList<Product>();	
			
		    String query =  buildQuery(wClause);
	  
	        try{ // read the invoice from the database
	        	Statement stmt = con.createStatement();
	        	stmt.setQueryTimeout(5);
	        	results = stmt.executeQuery(query);
		 	
		
				while( results.next() ){
					Product prodObj = new Product();
			     	prodObj = buildProduct(results);	
			     	list.add(prodObj);	
				}//end while
				
				stmt.close();  
				
				/*if(retrieveAssociation)
			    {   //The supervisor and department is to be build as well
					for(Employee empObj : list){
			            String superssn = empObj.getSupervisor().getSsn();
			            Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
			            empObj.setSupervisor(superEmp);
			            System.out.println("Supervisor is selected");
			           // here the department has to be selected as well
			        }
			    }*/   
					
			}//end try	
		 	catch(Exception e){
		 		System.out.println("Query exception - select: "+e);
				e.printStackTrace();
		 	}
	        
			return list;
		}
	    
	    private Product singleWhere(String wClause, boolean retrieveAssociation)
		{
			ResultSet results;
			Product prodObj = new Product();
	                
	        String query =  buildQuery(wClause);
	        System.out.println(query);
	        
			try{ // read the employee from the database
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 		results = stmt.executeQuery(query);
		 		
		 		if( results.next() ){
		 			prodObj = buildProduct(results);
	                //association is to be build
	                stmt.close();
	                
	                /*if(retrieveAssociation)
	                {   
	                    DBSalesOrder dbsalesObj = new DBSalesOrder();
	                    custObj.setSalesOrderList(dbsalesObj.findCostumerSalesOrder(custObj.getCustomerId(), false));                   
	                }*/
				}
	            else{ //no invoice was found
	            	prodObj = null;
	            }
			}//end try	
		 	catch(Exception e){
		 		System.out.println("Query exception: "+e);
		 	}
			return prodObj;
		}
		
		
		private String buildQuery(String wClause)
		{
		    String query="SELECT productId, suppId, name, type, purchasePrice, salesPrice," +
		    		"rentPrice, countryOfOrigin, minStock, " +"size, colour, eqType, " +
		    				"description, fabric, calibre FROM product";		
			if (wClause.length()>0)
				query=query+" WHERE "+ wClause;
				
			return query;
		}
		
		// create a product for the table
		private Product buildProduct(ResultSet results){
			Product productObj = new Product();
	        try{ // the columns from the table product are used
	              productObj.setProductId(results.getString("productId"));
	              productObj.setSuppId(results.getString("suppId"));
	              productObj.setName(results.getString("name"));
	              productObj.setType(results.getString("type"));
	              productObj.setPurchasePrice(results.getInt("purchasePrice"));
	              productObj.setSalesPrice(results.getInt("salesPrice"));
	              productObj.setRentPrice(results.getInt("rentPrice"));
	              productObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
	              productObj.setMinStock(results.getInt("minStock"));
	              productObj.setSize(results.getString("size"));
	              productObj.setColour(results.getString("colour"));
	              productObj.setEqType(results.getString("eqType"));
	              productObj.setDescription(results.getString("description"));
	              productObj.setFabric(results.getString("fabric"));
	              productObj.setCalibre(results.getString("calibre"));
	       }
	       catch(Exception e)
	       {
	           System.out.println("Error in building the product object");
	       }
	       return productObj;
	    }
		
		
		
		
	}
